package scct.zhang.dao;

import scct.zhang.model.Group;
import scct.zhang.utils.DBUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class GroupDao {
    public Group login(String groupname,String creatorname, String createtime) {
        Group group = null;
//    获取数据库连接对象
        Connection conn = DBUtils.getConnection();
        String sql = "select*from sm_group where groupname=? and creatorname=? and createtime=?";
//    预编译查询对象
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);

            pstmt.setString(1,groupname);
            pstmt.setString(2,creatorname);
            pstmt.setString(3, createtime);
//        查询
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                int id = rs.getInt("id");
                String groupname2 = rs.getString("groupname");
                String creatorname2 = rs.getString("creatorname");
                String createtime2 = rs.getString("createtime");
                group = new Group(id,groupname2,creatorname2,createtime2);
            }
            DBUtils.closeConnection(conn);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return group;
    }

    /**
     * @param groupname 用户名
     * @param creatorname 密码
     * @param createtime
     * @return 用户对象
     */
    //    添加
    public boolean addGroup(String groupname,String creatorname,String createtime) {
        int result = 0;
        Connection conn = DBUtils.getConnection();
        String sql = "insert into sm_group values(null,?,?,?)";
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(3,createtime);
            pstmt.setString(2,creatorname);
            pstmt.setString(1,groupname);
            result = pstmt.executeUpdate();
        } catch (SQLException e){
            throw new RuntimeException(e);
        }
        return result == 1;
    }

    public boolean isDupname(int id){
        int count = 0;// 查到联系人的数量
        Connection conn = DBUtils.getConnection();
        String sql = "select*from sm_group where id=?";
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,id);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()){
                count =1;
            }
//            DBUtils.closeConnection(conn);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return count>0;
    }

    public List<Group> getAllGroup() {
        Connection conn = DBUtils.getConnection();
        List<Group> groups = new ArrayList<>();

        String sql = "select * from sm_group";
        try {
            PreparedStatement pstme = conn.prepareStatement(sql);
            ResultSet rs = pstme.executeQuery();
            while (rs.next()) {
                int id = rs.getInt("id");
                String groupname =rs.getString("groupname");
                String creatorname = rs.getString("creatorname");
                String createtime = rs.getString("createtime");
                groups.add(new Group(id,groupname, creatorname,createtime));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return groups;
    }

    public Group getGroupId(int id){
        Group group = null;
        Connection conn = DBUtils.getConnection();
        String sql = "select*from sm_group where id=?";
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,id);

            ResultSet rs = pstmt.executeQuery();
            if (rs.next()){
                String groupname = rs.getString("groupname");
                String creatorname = rs.getString("creatorname");
                String createtime = rs.getString("createtime");
                group = new Group(id,groupname,creatorname,createtime);

            }
            DBUtils.closeConnection(conn);

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return group;
    }

    public boolean deleteGroup(int id) {
        int num = 0;
        Connection conn = DBUtils.getConnection();
        String sqlStr = "delete from sm_group where id = ?";
        try{
            PreparedStatement pstmt = conn.prepareStatement(sqlStr);
            pstmt.setInt(1,id);
            num = pstmt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return num > 0;
    }

    public boolean updateGroup(int id,String groupname,String creatorname,String createtime){
        int count = 0;
        Connection conn = DBUtils.getConnection();
        String update_sql = "update sm_group set groupname=?,creatorname=?,createtime=? where id = ?";
        try {
            PreparedStatement pstmt = conn.prepareStatement(update_sql);
            pstmt.setString(3,createtime);
            pstmt.setString(2,creatorname);
            pstmt.setString(1,groupname);
            pstmt.setInt(4,id);
            count = pstmt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return count > 0;
    }

//    public ArrayList<Group> getGroupByName(String name) {
//        ArrayList<Group> groups = new ArrayList<>();
//        Connection conn = DBUtils.getConnection();
//        String sql = "select * from sm_group where name like '%" + name + "%'";
//        try {
//            PreparedStatement pstmt = conn.prepareStatement(sql);
//            ResultSet rs = pstmt.executeQuery();
//            while (rs.next()) {
//                int id = rs.getInt("id");
//                String groupname2 = rs.getString("groupname");
//                String creatorname = rs.getString("creatorname");
//                String createtime = rs.getString("createtime");
//                groups.add(new Group(id, groupname2,creatorname,createtime));
//            }
//        } catch (SQLException e) {
//            throw new RuntimeException(e);
//        }
//        return groups;
//    }


}
